﻿using System;
using System.Collections.Generic;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;

/*
	TODO: 
*/

namespace ExcelUtils
{
	
	public class ExcelBuilder
	{
		// the target filename
		public string FileName {get; set;}
		
		private HSSFWorkbook Workbook {get; set;}

		// Name each style so we can add them
		Dictionary<String, ICellStyle> Styles {get; set;}
		Dictionary<String, IFont> Fonts {get; set;}
		Dictionary<String, ISheet> Sheets {get; set;}

		public ExcelBuilder(string fileName)
		{
			this.FileName = fileName;
			// add xls?
			this.Workbook = new HSSFWorkbook();
			this.Fonts = new Dictionary<string,IFont>();
			this.Styles = new Dictionary<string,ICellStyle>();
		}
		
		// This API protects the caller from the NPOI objects.
		
		public enum FontColour {normal, black, red, blue, green, yellow};
		// create a font to add to a style
		// boldItalicFlags should contain B and/or I to set bold/italic or both
		// colourIndex is eg NPOI.HSSF.Util.HSSFColor.BLUE.index
		public void MakeFont(string fontKeyName, string fontName, int pointSize, string boldItalicFlags, FontColour colour)
		{
			short colourIndex = (colour == FontColour.black) ? NPOI.HSSF.Util.HSSFColor.BLACK.index :
								(colour == FontColour.red) ? NPOI.HSSF.Util.HSSFColor.RED.index : 
								(colour == FontColour.green) ? NPOI.HSSF.Util.HSSFColor.GREEN.index : 
								(colour == FontColour.blue) ? NPOI.HSSF.Util.HSSFColor.BLUE.index : (short)-1;

			IFont font = MakeFont(this.Workbook, fontName, pointSize, boldItalicFlags, colourIndex);
			this.Fonts.Add(fontKeyName.ToUpper(), font);
		}
		// Add a style to the sheet 
		// leftRightCentreAlign is blank=L, L,R or C for horizontal alignement
		// topMiddleBottomAlign is blank=default T,C or B for vertical alignment
		public void AddStyle(String styleKeyName, string fontKeyName, string leftRightCentreAlign, string topMiddleBottomAlign, bool wrapText)
		{
			IFont font = Fonts[fontKeyName.ToUpper()];
			ICellStyle style = MakeStyle(this.Workbook, font, leftRightCentreAlign, topMiddleBottomAlign, wrapText);
			this.Styles.Add(styleKeyName.ToUpper(), style);
		} 
		
		public static IFont MakeFont(HSSFWorkbook workbook, string name, int size, string bi, short colourIndex)
		{
			IFont font = workbook.CreateFont();
			if (!string.IsNullOrEmpty(name.Trim()))
				font.FontName = name;
			if (size > 0)
				font.FontHeightInPoints = (short)size;
			if (colourIndex != -1)
				font.Color = colourIndex;
			if (bi.ToUpper().IndexOf("B") != -1)
				font.Boldweight = 900;
			if (bi.ToUpper().IndexOf("I") != -1)
				font.IsItalic = true;
			return font;
		}
		
		public enum BroadType {Null, String, Integer, Number, Date, Bool};
		
		public static BroadType GetBroadType(object o)
		{
			if (o == null) return BroadType.Null;
			Type t = o.GetType();
			return GetBroadType(t);
		}

		public static BroadType GetBroadType(Type t)
		{
			return (t == typeof(int) || t == typeof(Int32) || t == typeof(Int64) || t == typeof(Int16)) ? BroadType.Integer :
				   (t == typeof(double) || t == typeof(decimal)) ? BroadType.Number :
				   (t == typeof(DateTime)) ? BroadType.Date :
				   (t == typeof(bool)) ? BroadType.Bool :
				   (t == typeof(string)) ? BroadType.String :
				   (t == typeof(System.DBNull)) ? BroadType.Null
					: SetUnknownType(t, BroadType.String);
		}

		private static BroadType SetUnknownType(Type t, BroadType broadType)
		{
			// for later dev.
			// Console.WriteLine("Setting type {0} to {1}", t, broadType);
			return broadType;
		}

		public static ICellStyle MakeStyle(HSSFWorkbook workbook, IFont font, string lrc, string vlrc, bool wrapText)
		{
			// Create the style object
			var style = workbook.CreateCellStyle();
			style.SetFont(font);

			// Horizontal Alignment
			if (lrc.ToUpper().Contains("R"))
				style.Alignment = HorizontalAlignment.RIGHT;
			else if (lrc.ToUpper().Contains("C"))
				style.Alignment = HorizontalAlignment.CENTER;
			else if (lrc.ToUpper().Contains("L"))
				style.Alignment = HorizontalAlignment.LEFT;
			// Vertical Alignment
			if (vlrc.ToUpper().Contains("B"))
				style.VerticalAlignment = VerticalAlignment.BOTTOM;
			else if (vlrc.ToUpper().Contains("T"))
				style.VerticalAlignment = VerticalAlignment.TOP;
			else if (vlrc.ToUpper().Contains("C") || vlrc.ToUpper().Contains("M")) // Middle or Center
				style.VerticalAlignment = VerticalAlignment.CENTER;

			style.WrapText = wrapText;
			
			return style;
		}
		
		public static ICellStyle MakeStyle(HSSFWorkbook workbook, ICellStyle sourceStyle)
		{
			var style = workbook.CreateCellStyle();
			style.CloneStyleFrom(sourceStyle);
			return style;
		}

		public static void SetColumnWidths(ISheet sheet, int[] cellWidths)
		{
			for (int idx = 0; idx < cellWidths.Length; ++idx)
				sheet.SetColumnWidth(idx, cellWidths[idx] * 256);
		}

		public static void WriteFile(HSSFWorkbook workbook, string fileName)
		{
			using (var fileData = new FileStream(fileName, FileMode.Create))
			{
				workbook.Write(fileData);
			}
		}

		public static ICell AddCell(IRow row, int cellIndex, ICellStyle style, object data)
		{
			ICell cell = row.CreateCell(cellIndex);
			if (style != null)
			    cell.CellStyle = style;
			BroadType dataType = GetBroadType(data);
			try
			{
				switch (dataType)
				{
					case BroadType.Null: cell.SetCellValue(""); break;
					case BroadType.Integer: cell.SetCellValue(System.Convert.ToInt32(data)); break;
					case BroadType.Number: cell.SetCellValue(System.Convert.ToDouble(data)); break;
					case BroadType.Bool: cell.SetCellValue((bool)data); break;
					case BroadType.Date: cell.SetCellValue((DateTime)data); break;
					default: cell.SetCellValue(data.ToString()); break;
				}
			}
			catch (InvalidCastException ice)
			{
				Console.WriteLine(ice.Message);
				Console.WriteLine("col: {0} {1} to {2}", cellIndex, data.GetType(), dataType);
			}
			return cell;
		}

		public static short RegisterSetCellFormat(HSSFWorkbook workbook, String dateFormat)
		{
			var builtinFormatId = HSSFDataFormat.GetBuiltinFormat(dateFormat);
			if (builtinFormatId == - 1)
			{
				// not a built-in format, so create a new one
				var newDataFormat = workbook.CreateDataFormat();
				builtinFormatId = newDataFormat.GetFormat(dateFormat);
			}
			return builtinFormatId;
		}
	}

	public class ExcelExporter
	{
		public string FileName {get; set; }
		public ExcelBuilder Builder {get; set; } 
		public DataTable Data {get; set; }
		public string FontName {get; set;}
		public int FontPointSize {get; set;}
		public enum HeaderOption {None, Header, HeaderAndSpace};
		public HeaderOption Header{ get; set; }
		public ICellStyle HeaderStyleLeft{get; set;}
		public ICellStyle HeaderStyleRight{get; set;}
		public ICellStyle BodyStyleLeft{get; set;}
		public ICellStyle BodyStyleRight{get; set;}
		public ICellStyle LongTextStyle{get; set;}
		public ICellStyle DateStyle{get; set;}
		public ICellStyle DateTimeStyle{get; set;}
		public ICellStyle NumberStyle{get; set;}
		public HSSFWorkbook Workbook {get; set; }
		public int MaxCharWidth {get; set; }
		public String LastError {get; set; }
		private List<string[]> summary = new List<string[]>();

		public ExcelExporter(String fileName, DataTable table, string fontName, int fontPointSize)
		{
			FileName = fileName;
			Data = table;
			FontName = fontName;
			FontPointSize = fontPointSize;
			LastError = ""; 
			MaxCharWidth = 80;
			Header = HeaderOption.Header;
			InitialiseBuilder();
		}

		public void AddSummaryItem(string title, string value)
		{
			summary.Add(new string[] {title, value});
		}

		private void InitialiseBuilder()
		{
			Builder = new ExcelBuilder(FileName);
			Workbook = new HSSFWorkbook();
			IFont header = ExcelBuilder.MakeFont(Workbook, FontName, FontPointSize, "B", -1);
			IFont data = ExcelBuilder.MakeFont(Workbook, FontName, FontPointSize, "", -1);
			
			HeaderStyleLeft = ExcelBuilder.MakeStyle(Workbook, header, "L", "T", false); // don't wrap headers
			HeaderStyleRight = ExcelBuilder.MakeStyle(Workbook, header, "R", "T", false);
			
			BodyStyleLeft = ExcelBuilder.MakeStyle(Workbook, data, "L", "T", false);

			BodyStyleRight = ExcelBuilder.MakeStyle(Workbook, data, "R", "T", false);
			LongTextStyle =  ExcelBuilder.MakeStyle(Workbook, BodyStyleLeft);
			LongTextStyle.WrapText = true; // wrap long text.
			DateStyle = ExcelBuilder.MakeStyle(Workbook, BodyStyleRight);
			DateTimeStyle = ExcelBuilder.MakeStyle(Workbook, DateStyle);
			NumberStyle = ExcelBuilder.MakeStyle(Workbook, BodyStyleRight);
			
			short dateFormat = ExcelBuilder.RegisterSetCellFormat(Workbook, "m/d/yy");
			DateStyle.DataFormat = dateFormat;

			short dtFormat = ExcelBuilder.RegisterSetCellFormat(Workbook, "m/d/yy h:mm");
			DateTimeStyle.DataFormat = dtFormat;

			short numberFormat = ExcelBuilder.RegisterSetCellFormat(Workbook, "#,##0.00");
			NumberStyle.DataFormat = numberFormat;

		}

		public bool Export()
		{
			// Clear/set the last error message before starting anythinbg
			LastError = ""; 
			bool completed = false;
			// Cope with the case where someone already has the file open in Excel
			// make a temp file
			String tempFilename = FileName + ".tmp";
			try
			{
				// try create. Catch error below
				File.Create(tempFilename).Close();

				var sheet = Workbook.CreateSheet(Data.TableName);
				// set column widths
				bool useHeaderInWidthCalculation = (Header == HeaderOption.Header || Header == HeaderOption.HeaderAndSpace);
				SetColumnWidths(sheet, useHeaderInWidthCalculation);
				// WriteHeaderRow
				int dataRowOffset = WriteHeaderRow(sheet, 0, 0, Header);
				// Write body
				WriteBody(sheet, Data.Columns.Count, dataRowOffset);  
				// Summary 
				if (this.summary.Count > 0)
				{
					var summarySheet = Workbook.CreateSheet("Summary");
					this.AddSummarySheet(summarySheet, this.summary);
				}
				// Done
				ExcelBuilder.WriteFile(Workbook, tempFilename);
				// Now do the rename delete bit
				if (File.Exists(FileName))
					File.Delete(FileName);
				// and rename
				File.Move(tempFilename, FileName);
				completed = true;
			}
			catch(System.IO.DirectoryNotFoundException ednf) 
			{
				LastError = String.Format("Cannot create output file {0}.\n Possible problem with the path or name.", tempFilename);
			}
			catch(System.UnauthorizedAccessException eunauth)
			{
				LastError = String.Format("You do not have permission to create a file in\ndirectory {0}", Path.GetDirectoryName(tempFilename));
			}
			catch(System.IO.IOException e)  // get the correct exception 
			{
				if (IsAccessError(e.Message))
				{
					String message = @"Cannot create {0}
										The most likely cause is that an old version is still open in Excel. If
										so then you will need to close the spreadsheet in Excel then either 
										rerun the export. If not then check the error message below and take 
										appropriate action.
										------------------------------------------------------------------------
										{1}".Replace("\t", "");
					LastError = String.Format(message, FileName, e.Message);
				}
				else
				{
					LastError = String.Format("Could not complete the export. The error message is\n{0}", e.Message);
				}
			}
			catch(Exception ex) 
			{
				LastError = String.Format("Could not complete the export. The error message is\n{0}", ex.Message);
			} 
			return completed;
		}

		private bool IsAccessError(string p)
		{
			return p.Contains("cannot access");
		}



		private int WriteBody(ISheet sheet, int columnCount,int rowIndex)
		{
			foreach (DataRow dr in Data.Rows)
			{
				var row = sheet.CreateRow(rowIndex++);
				for (int idx = 0; idx < columnCount; ++idx)
				{
					ICellStyle style = BodyStyleFromDataType(dr[idx]);
					ExcelBuilder.AddCell(row, idx, style, dr[idx]);
				}
			}
			return rowIndex;
		}

		private ICellStyle BodyStyleFromDataType(object data)
		{
			ExcelBuilder.BroadType dataType = ExcelBuilder.GetBroadType(data);
			return (dataType == ExcelBuilder.BroadType.Integer) ? BodyStyleRight :
					(dataType == ExcelBuilder.BroadType.Number) ? NumberStyle :
					(dataType == ExcelBuilder.BroadType.Date) ? SelectDateStyle(data) : 
					(dataType == ExcelBuilder.BroadType.String) ? SelectTextStyle(data) : 
					BodyStyleLeft;
		}

		private ICellStyle SelectTextStyle(object data)
		{
			String theData = data.ToString();
			if (theData.Length <= MaxCharWidth) // the column should be sized for this
				return BodyStyleLeft;			// ordinary Left justify
			// find first space in the data
			int spacePos = theData.IndexOf(' ');
			// if no space or its beyond the column width
			// doesn't seem like a good field to wrap
			if (spacePos == -1 || spacePos > MaxCharWidth)
				return BodyStyleLeft;
			// normal long text with words in so apply the wrapping style.
			return LongTextStyle;
		}

		private ICellStyle SelectDateStyle(object data)
		{
			if (data == null) return BodyStyleLeft;
			if (data.GetType() != typeof(DateTime)) return BodyStyleLeft;
			DateTime dt = (DateTime)data;
			if (dt.Hour == 0 && dt.Minute == 0 && dt.Second == 0)
				return DateStyle;
			return DateTimeStyle;
		}

		private int WriteHeaderRow(ISheet sheet, int rowIndex, int columnIndex, HeaderOption headerOption)
		{
			// test for the none option. Return to start data at row 0
			if (headerOption == HeaderOption.None)
				return 0;

			// At this point we want the row.
			var row = sheet.CreateRow(rowIndex);
			foreach (DataColumn dc in Data.Columns)
			{
				ExcelBuilder.BroadType bt = ExcelBuilder.GetBroadType(dc.DataType);
				ICellStyle style = (bt == ExcelBuilder.BroadType.Integer || 
									bt == ExcelBuilder.BroadType.Number ||
									bt == ExcelBuilder.BroadType.Date) ? HeaderStyleRight : HeaderStyleLeft;
				ExcelBuilder.AddCell(row, columnIndex++, style, dc.ColumnName);
			}
			return (headerOption == HeaderOption.Header) ? 1 :
					(headerOption == HeaderOption.HeaderAndSpace) ? 2  // sending back to start data at row 2 adds the space 
					: 1; // won't be here unless code above moves on
		}

		private void AddSummarySheet(ISheet summarySheet, List<string[]> summaryItems)
		{
			ExcelBuilder.SetColumnWidths(summarySheet, new int[] {30, 120});
			var row = summarySheet.CreateRow(0);
			ExcelBuilder.AddCell(row, 0, HeaderStyleLeft, "Summary");
			
			int rowIdx = 2;
			foreach (string[] summaryItem in summaryItems)
			{
				row = summarySheet.CreateRow(rowIdx++);
				ExcelBuilder.AddCell(row, 0, HeaderStyleLeft, summaryItem[0]);
				ExcelBuilder.AddCell(row, 1, LongTextStyle, summaryItem[1]);
			}

		}

		private void SetColumnWidths(ISheet sheet, bool IncludeHeader)
		{
			List<int> columnWidths = new List<int>();
			foreach (DataColumn dc in Data.Columns)
			{
				//int size = (IncludeHeader) ? (int)((double)dc.ColumnName.Length * 1.4D) // scale for boldness and capitalness
				//                            : 3;			// Set to a minimum to display the letter.
				int size = (IncludeHeader) ? CalcCharWidth(dc.ColumnName, true) // the function scales for boldness and capitalness
											: 3;			// Set to a minimum to display the letter.
				columnWidths.Add(size);
			}
			// check the data and resize accordingly
			int columnCount = Data.Columns.Count;
			foreach (DataRow dr in Data.Rows)
			{
				for (int idx = 0; idx < columnCount; ++idx)
				{
					int size = CalcCharWidth(dr[idx].ToString(), false);  // false assumes data is not bold.
					if (size > MaxCharWidth)
						size = MaxCharWidth;
					if (size > columnWidths[idx])
						columnWidths[idx] = size;
				}
			}
			ExcelBuilder.SetColumnWidths(sheet, columnWidths.ToArray());
		}

		private int CalcCharWidth(string text, bool isHeader)
		{
			// (1.1,1.2) good for BOLD UPPER
			double boldFactor = 1.5;
			double capFactor = 1.2;
			double minWidth = 4;   // don't let anything smaller than this out.
			double dVal = Convert.ToDouble(text.Length);
			if (isHeader) // implies bold
				dVal *= boldFactor;
			if (text == text.ToUpper())
				dVal *= capFactor;
			if (dVal < minWidth) // small text factor
				dVal = minWidth;
			// 
			return Convert.ToInt32(dVal);
		}
	}

}
